using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VMKRFMP
    {
        public DataRow Convert2MKRFMP(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryMKRFMP(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["MKFPMANDANT"] = " ".PadRight(2 ); 
         dwReturn["MKFPPAUFKRS"] = " ".PadRight(2 ); 
         dwReturn["MKFPPAUFLFD"] = " ".PadRight(6 ); 
         dwReturn["MKFPPAUFPOS"] = " ".PadRight(3 ); 
         dwReturn["MKFPAFOLGE"] = " ".PadRight(3 ); 
         dwReturn["MKFPTP"] = " ".PadRight(4 ); 
         dwReturn["MKFPPOS"] = " ".PadRight(2 ); 
         dwReturn["MKFPNEUB"] = " ".PadRight(8 ); 
         dwReturn["MKFPNEUD"] = " ".PadRight(0 ); 
         dwReturn["MKFPAENB"] = " ".PadRight(8 ); 
         dwReturn["MKFPAEND"] = " ".PadRight(0 ); 
         dwReturn["MKFPAENZL"] = " ".PadRight(0 ); 
         dwReturn["MKFPAUFKRS1"] = " ".PadRight(2 ); 
         dwReturn["MKFPAUFLFD1"] = " ".PadRight(6 ); 
         dwReturn["MKFPAUFPOS1"] = " ".PadRight(3 ); 
         dwReturn["MKFPTYP1"] = " ".PadRight(1 ); 
         dwReturn["MKFPPARTIENR1"] = " ".PadRight(12 ); 
         dwReturn["MKFPAUFKRS2"] = " ".PadRight(2 ); 
         dwReturn["MKFPAUFLFD2"] = " ".PadRight(6 ); 
         dwReturn["MKFPAUFPOS2"] = " ".PadRight(3 ); 
         dwReturn["MKFPTYP2"] = " ".PadRight(1 ); 
         dwReturn["MKFPPARTIENR2"] = " ".PadRight(12 ); 
         dwReturn["MKFPAUFKRS3"] = " ".PadRight(2 ); 
         dwReturn["MKFPAUFLFD3"] = " ".PadRight(6 ); 
         dwReturn["MKFPAUFPOS3"] = " ".PadRight(3 ); 
         dwReturn["MKFPTYP3"] = " ".PadRight(1 ); 
         dwReturn["MKFPPARTIENR3"] = " ".PadRight(12 ); 
         dwReturn["MKFPAUFKRS4"] = " ".PadRight(2 ); 
         dwReturn["MKFPAUFLFD4"] = " ".PadRight(6 ); 
         dwReturn["MKFPAUFPOS4"] = " ".PadRight(3 ); 
         dwReturn["MKFPTYP4"] = " ".PadRight(1 ); 
         dwReturn["MKFPPARTIENR4"] = " ".PadRight(12 ); 
         dwReturn["MKFPAUFKRS5"] = " ".PadRight(2 ); 
         dwReturn["MKFPAUFLFD5"] = " ".PadRight(6 ); 
         dwReturn["MKFPAUFPOS5"] = " ".PadRight(3 ); 
         dwReturn["MKFPTYP5"] = " ".PadRight(1 ); 
         dwReturn["MKFPPARTIENR5"] = " ".PadRight(12 ); 
         dwReturn["MKFPAUFKRS6"] = " ".PadRight(2 ); 
         dwReturn["MKFPAUFLFD6"] = " ".PadRight(6 ); 
         dwReturn["MKFPAUFPOS6"] = " ".PadRight(3 ); 
         dwReturn["MKFPTYP6"] = " ".PadRight(1 ); 
         dwReturn["MKFPPARTIENR6"] = " ".PadRight(12 ); 
         dwReturn["MKFPAUFKRS7"] = " ".PadRight(2 ); 
         dwReturn["MKFPAUFLFD7"] = " ".PadRight(6 ); 
         dwReturn["MKFPAUFPOS7"] = " ".PadRight(3 ); 
         dwReturn["MKFPTYP7"] = " ".PadRight(1 ); 
         dwReturn["MKFPPARTIENR7"] = " ".PadRight(12 ); 
         dwReturn["MKFPAUFKRS8"] = " ".PadRight(2 ); 
         dwReturn["MKFPAUFLFD8"] = " ".PadRight(6 ); 
         dwReturn["MKFPAUFPOS8"] = " ".PadRight(3 ); 
         dwReturn["MKFPTYP8"] = " ".PadRight(1 ); 
         dwReturn["MKFPPARTIENR8"] = " ".PadRight(12 ); 
         dwReturn["MKFPAUFKRS9"] = " ".PadRight(2 ); 
         dwReturn["MKFPAUFLFD9"] = " ".PadRight(6 ); 
         dwReturn["MKFPAUFPOS9"] = " ".PadRight(3 ); 
         dwReturn["MKFPTYP9"] = " ".PadRight(1 ); 
         dwReturn["MKFPPARTIENR9"] = " ".PadRight(12 ); 
         dwReturn["MKFPFILLER"] = " ".PadRight(136 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapMKRFMP(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveMKRFMP(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryMKRFMP(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapMKRFMP(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertMKRFMP(dwLocal, ITXDBVERSION);
            }
            UpdateMKRFMP(dwLocal);
        }
        public void InsertMKRFMP(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO MKRFMP(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryMKRFMP(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM MKRFMP WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM MKRFMP  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "MKRFMP";
            Return = Result;
            return Return;
        }
        public void DeleteMKRFMP(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM MKRFMP   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateMKRFMP(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE MKRFMP SET "
                    +"MKFPMANDANT= :MKFPMANDANT,"
                    +"MKFPPAUFKRS= :MKFPPAUFKRS,"
                    +"MKFPPAUFLFD= :MKFPPAUFLFD,"
                    +"MKFPPAUFPOS= :MKFPPAUFPOS,"
                    +"MKFPAFOLGE= :MKFPAFOLGE,"
                    +"MKFPTP= :MKFPTP,"
                    +"MKFPPOS= :MKFPPOS,"
                    +"MKFPNEUB= :MKFPNEUB,"
                    +"MKFPNEUD= :MKFPNEUD,"
                    +"MKFPAENB= :MKFPAENB,"
                    +"MKFPAEND= :MKFPAEND,"
                    +"MKFPAENZL= :MKFPAENZL,"
                    +"MKFPAUFKRS1= :MKFPAUFKRS1,"
                    +"MKFPAUFLFD1= :MKFPAUFLFD1,"
                    +"MKFPAUFPOS1= :MKFPAUFPOS1,"
                    +"MKFPTYP1= :MKFPTYP1,"
                    +"MKFPPARTIENR1= :MKFPPARTIENR1,"
                    +"MKFPAUFKRS2= :MKFPAUFKRS2,"
                    +"MKFPAUFLFD2= :MKFPAUFLFD2,"
                    +"MKFPAUFPOS2= :MKFPAUFPOS2,"
                    +"MKFPTYP2= :MKFPTYP2,"
                    +"MKFPPARTIENR2= :MKFPPARTIENR2,"
                    +"MKFPAUFKRS3= :MKFPAUFKRS3,"
                    +"MKFPAUFLFD3= :MKFPAUFLFD3,"
                    +"MKFPAUFPOS3= :MKFPAUFPOS3,"
                    +"MKFPTYP3= :MKFPTYP3,"
                    +"MKFPPARTIENR3= :MKFPPARTIENR3,"
                    +"MKFPAUFKRS4= :MKFPAUFKRS4,"
                    +"MKFPAUFLFD4= :MKFPAUFLFD4,"
                    +"MKFPAUFPOS4= :MKFPAUFPOS4,"
                    +"MKFPTYP4= :MKFPTYP4,"
                    +"MKFPPARTIENR4= :MKFPPARTIENR4,"
                    +"MKFPAUFKRS5= :MKFPAUFKRS5,"
                    +"MKFPAUFLFD5= :MKFPAUFLFD5,"
                    +"MKFPAUFPOS5= :MKFPAUFPOS5,"
                    +"MKFPTYP5= :MKFPTYP5,"
                    +"MKFPPARTIENR5= :MKFPPARTIENR5,"
                    +"MKFPAUFKRS6= :MKFPAUFKRS6,"
                    +"MKFPAUFLFD6= :MKFPAUFLFD6,"
                    +"MKFPAUFPOS6= :MKFPAUFPOS6,"
                    +"MKFPTYP6= :MKFPTYP6,"
                    +"MKFPPARTIENR6= :MKFPPARTIENR6,"
                    +"MKFPAUFKRS7= :MKFPAUFKRS7,"
                    +"MKFPAUFLFD7= :MKFPAUFLFD7,"
                    +"MKFPAUFPOS7= :MKFPAUFPOS7,"
                    +"MKFPTYP7= :MKFPTYP7,"
                    +"MKFPPARTIENR7= :MKFPPARTIENR7,"
                    +"MKFPAUFKRS8= :MKFPAUFKRS8,"
                    +"MKFPAUFLFD8= :MKFPAUFLFD8,"
                    +"MKFPAUFPOS8= :MKFPAUFPOS8,"
                    +"MKFPTYP8= :MKFPTYP8,"
                    +"MKFPPARTIENR8= :MKFPPARTIENR8,"
                    +"MKFPAUFKRS9= :MKFPAUFKRS9,"
                    +"MKFPAUFLFD9= :MKFPAUFLFD9,"
                    +"MKFPAUFPOS9= :MKFPAUFPOS9,"
                    +"MKFPTYP9= :MKFPTYP9,"
                    +"MKFPPARTIENR9= :MKFPPARTIENR9,"
                    +"MKFPFILLER= :MKFPFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("MKFPMANDANT", OracleType.NVarChar).Value = dwData["MKFPMANDANT"].ToString(); 
          or1.Parameters.Add("MKFPPAUFKRS", OracleType.NVarChar).Value = dwData["MKFPPAUFKRS"].ToString(); 
          or1.Parameters.Add("MKFPPAUFLFD", OracleType.NVarChar).Value = dwData["MKFPPAUFLFD"].ToString(); 
          or1.Parameters.Add("MKFPPAUFPOS", OracleType.NVarChar).Value = dwData["MKFPPAUFPOS"].ToString(); 
          or1.Parameters.Add("MKFPAFOLGE", OracleType.NVarChar).Value = dwData["MKFPAFOLGE"].ToString(); 
          or1.Parameters.Add("MKFPTP", OracleType.NVarChar).Value = dwData["MKFPTP"].ToString(); 
          or1.Parameters.Add("MKFPPOS", OracleType.NVarChar).Value = dwData["MKFPPOS"].ToString(); 
          or1.Parameters.Add("MKFPNEUB", OracleType.NVarChar).Value = dwData["MKFPNEUB"].ToString(); 
          or1.Parameters.Add("MKFPNEUD", OracleType.NVarChar).Value = dwData["MKFPNEUD"].ToString(); 
          or1.Parameters.Add("MKFPAENB", OracleType.NVarChar).Value = dwData["MKFPAENB"].ToString(); 
          or1.Parameters.Add("MKFPAEND", OracleType.NVarChar).Value = dwData["MKFPAEND"].ToString(); 
          or1.Parameters.Add("MKFPAENZL", OracleType.NVarChar).Value = dwData["MKFPAENZL"].ToString(); 
          or1.Parameters.Add("MKFPAUFKRS1", OracleType.NVarChar).Value = dwData["MKFPAUFKRS1"].ToString(); 
          or1.Parameters.Add("MKFPAUFLFD1", OracleType.NVarChar).Value = dwData["MKFPAUFLFD1"].ToString(); 
          or1.Parameters.Add("MKFPAUFPOS1", OracleType.NVarChar).Value = dwData["MKFPAUFPOS1"].ToString(); 
          or1.Parameters.Add("MKFPTYP1", OracleType.NVarChar).Value = dwData["MKFPTYP1"].ToString(); 
          or1.Parameters.Add("MKFPPARTIENR1", OracleType.NVarChar).Value = dwData["MKFPPARTIENR1"].ToString(); 
          or1.Parameters.Add("MKFPAUFKRS2", OracleType.NVarChar).Value = dwData["MKFPAUFKRS2"].ToString(); 
          or1.Parameters.Add("MKFPAUFLFD2", OracleType.NVarChar).Value = dwData["MKFPAUFLFD2"].ToString(); 
          or1.Parameters.Add("MKFPAUFPOS2", OracleType.NVarChar).Value = dwData["MKFPAUFPOS2"].ToString(); 
          or1.Parameters.Add("MKFPTYP2", OracleType.NVarChar).Value = dwData["MKFPTYP2"].ToString(); 
          or1.Parameters.Add("MKFPPARTIENR2", OracleType.NVarChar).Value = dwData["MKFPPARTIENR2"].ToString(); 
          or1.Parameters.Add("MKFPAUFKRS3", OracleType.NVarChar).Value = dwData["MKFPAUFKRS3"].ToString(); 
          or1.Parameters.Add("MKFPAUFLFD3", OracleType.NVarChar).Value = dwData["MKFPAUFLFD3"].ToString(); 
          or1.Parameters.Add("MKFPAUFPOS3", OracleType.NVarChar).Value = dwData["MKFPAUFPOS3"].ToString(); 
          or1.Parameters.Add("MKFPTYP3", OracleType.NVarChar).Value = dwData["MKFPTYP3"].ToString(); 
          or1.Parameters.Add("MKFPPARTIENR3", OracleType.NVarChar).Value = dwData["MKFPPARTIENR3"].ToString(); 
          or1.Parameters.Add("MKFPAUFKRS4", OracleType.NVarChar).Value = dwData["MKFPAUFKRS4"].ToString(); 
          or1.Parameters.Add("MKFPAUFLFD4", OracleType.NVarChar).Value = dwData["MKFPAUFLFD4"].ToString(); 
          or1.Parameters.Add("MKFPAUFPOS4", OracleType.NVarChar).Value = dwData["MKFPAUFPOS4"].ToString(); 
          or1.Parameters.Add("MKFPTYP4", OracleType.NVarChar).Value = dwData["MKFPTYP4"].ToString(); 
          or1.Parameters.Add("MKFPPARTIENR4", OracleType.NVarChar).Value = dwData["MKFPPARTIENR4"].ToString(); 
          or1.Parameters.Add("MKFPAUFKRS5", OracleType.NVarChar).Value = dwData["MKFPAUFKRS5"].ToString(); 
          or1.Parameters.Add("MKFPAUFLFD5", OracleType.NVarChar).Value = dwData["MKFPAUFLFD5"].ToString(); 
          or1.Parameters.Add("MKFPAUFPOS5", OracleType.NVarChar).Value = dwData["MKFPAUFPOS5"].ToString(); 
          or1.Parameters.Add("MKFPTYP5", OracleType.NVarChar).Value = dwData["MKFPTYP5"].ToString(); 
          or1.Parameters.Add("MKFPPARTIENR5", OracleType.NVarChar).Value = dwData["MKFPPARTIENR5"].ToString(); 
          or1.Parameters.Add("MKFPAUFKRS6", OracleType.NVarChar).Value = dwData["MKFPAUFKRS6"].ToString(); 
          or1.Parameters.Add("MKFPAUFLFD6", OracleType.NVarChar).Value = dwData["MKFPAUFLFD6"].ToString(); 
          or1.Parameters.Add("MKFPAUFPOS6", OracleType.NVarChar).Value = dwData["MKFPAUFPOS6"].ToString(); 
          or1.Parameters.Add("MKFPTYP6", OracleType.NVarChar).Value = dwData["MKFPTYP6"].ToString(); 
          or1.Parameters.Add("MKFPPARTIENR6", OracleType.NVarChar).Value = dwData["MKFPPARTIENR6"].ToString(); 
          or1.Parameters.Add("MKFPAUFKRS7", OracleType.NVarChar).Value = dwData["MKFPAUFKRS7"].ToString(); 
          or1.Parameters.Add("MKFPAUFLFD7", OracleType.NVarChar).Value = dwData["MKFPAUFLFD7"].ToString(); 
          or1.Parameters.Add("MKFPAUFPOS7", OracleType.NVarChar).Value = dwData["MKFPAUFPOS7"].ToString(); 
          or1.Parameters.Add("MKFPTYP7", OracleType.NVarChar).Value = dwData["MKFPTYP7"].ToString(); 
          or1.Parameters.Add("MKFPPARTIENR7", OracleType.NVarChar).Value = dwData["MKFPPARTIENR7"].ToString(); 
          or1.Parameters.Add("MKFPAUFKRS8", OracleType.NVarChar).Value = dwData["MKFPAUFKRS8"].ToString(); 
          or1.Parameters.Add("MKFPAUFLFD8", OracleType.NVarChar).Value = dwData["MKFPAUFLFD8"].ToString(); 
          or1.Parameters.Add("MKFPAUFPOS8", OracleType.NVarChar).Value = dwData["MKFPAUFPOS8"].ToString(); 
          or1.Parameters.Add("MKFPTYP8", OracleType.NVarChar).Value = dwData["MKFPTYP8"].ToString(); 
          or1.Parameters.Add("MKFPPARTIENR8", OracleType.NVarChar).Value = dwData["MKFPPARTIENR8"].ToString(); 
          or1.Parameters.Add("MKFPAUFKRS9", OracleType.NVarChar).Value = dwData["MKFPAUFKRS9"].ToString(); 
          or1.Parameters.Add("MKFPAUFLFD9", OracleType.NVarChar).Value = dwData["MKFPAUFLFD9"].ToString(); 
          or1.Parameters.Add("MKFPAUFPOS9", OracleType.NVarChar).Value = dwData["MKFPAUFPOS9"].ToString(); 
          or1.Parameters.Add("MKFPTYP9", OracleType.NVarChar).Value = dwData["MKFPTYP9"].ToString(); 
          or1.Parameters.Add("MKFPPARTIENR9", OracleType.NVarChar).Value = dwData["MKFPPARTIENR9"].ToString(); 
          or1.Parameters.Add("MKFPFILLER", OracleType.NVarChar).Value = dwData["MKFPFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
